﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.ServiceModel.Syndication;
using System.Diagnostics;
using System.Text;

namespace Microsoft.SqlCommunity.SQLRSS
{
    /// <summary>
    /// Perhaps the most important class in the application.  This class represents a database table that we want to monitor changes and 
    /// provide those changes as an RSS or Atom feed to a client via a WCF based web service.
    /// </summary>
    public class TableDescription : ObjectDescription
    {
        // Imported from Config file. See FeedMap.xsd for a more precise definition of this group of data members.

        // What to use as the title of the syndication feed item.  TitleMode  controls how the Title is interpreted.
        public string Title { get; set; }

        // Determines how to interpret Title.
        // If TitleMode is "Format" then Title should be
        // a string which contains text to be output to the syndication feed item title.  The text is used 
        // literally except items within curly brackets.  Primarily the text within the curly bracket is the name of
        // either a physical column or a DisplayName (logical column) from a DataView in either the table
        // being tracked or in a table joined to the table being tracked (a ReferenceTable).  Punctuation and 
        // other non-constiutents within the curly backets are displayed as literals along with the data retrieved 
        // from the physical or logical column if the contents of the column in the changed row (or related row) 
        // are not null. 
        // If TitleMode is "Function" then Title is the name of a scalar user defined function to be invoked.  The function
        // should return a string (nvarchar) which will be used as the title of syndication feed item.
        public string TitleMode { get; set; }

        // What to use as the description of the syndication feed item.  DescriptionMode controls 
        // how Description is interpreted.
        public string Description { get; set; }

        // Determines how to interpret Description.
        // If DescriptionMode is "Format" then Description should be
        // a string which contains text to be output to the syndication feed item title.  The text is used 
        // literally except items within curly brackets.  Primarily the text within the curly bracket is the name of
        // either a physical column or a DisplayName (logical column) from a DataView in either the table
        // being tracked or in a table joined to the table being tracked (a ReferenceTable).  Punctuation and 
        // other non-constiutents within the curly backets are displayed as literals along with the data retrieved 
        // from the physical or logical column if the contents of the column in the changed row (or related row) 
        // are not null. 
        // If DescriptionMode is "Function" then Description is the name of a scalar user defined function to be invoked.  
        // The function should return a string (nvarchar) which will be used as the description of syndication feed item.
        // This is analagous to TitleMode.  See above.
        public string DescriptionMode { get; set; }

        // Optionally determines which column should be used to supply the date for the syndication feed item.  
        // If not specified the time at which the change is noticed will be used. It is good to supply the 
        // actual date column if possible.
        public string DateColumn { get; set; }

        // A value or values to use in order to filter the results sent to the syndication feed.  ChangeFilterMode
		// controls how these values are interpreted.
        public string ChangeFilter { get; set; }

        // Controls how ChangeFilter is interpreted.  If ChangeFilterMode is "Top" then ChangeFilter should be 
        // a positive integer.  The syndication feed will get the top n rows rendered as a syndication item per row.
        // if ChangeFilterMode is "TimeInterval" then ChangeFilter should be a time interval of the form [[hh:]mm:]ss where 
        // the square brackets indicate optional items and should not be typed literally, and hh is two digits of optional 
        // hours, mm is optional minutes, and ss is mandatory seconds.    The syndication
        // feed will get only get items that lie within the time interval between now and now minus the specified time interval.
        public string ChangeFilterMode { get; set; }

        // Optionally determines where data comes from.  DataViewSourceMode controls how the
        // DataViewSource is interpreted.  If no DataViewSource is specified the name of 
        // the Table is used.
        public string DataViewSource { get; set; }

        // When present and subject to the DataDisplayMode setting, DataViewSourceMode determines how 
        // data is gathered to present to the user if the user clicks on the link associated with a syndication feed item.
        // If DataViewSourceMode is "Table" then DataViewSource must be the name of a table to be
        // displayed.  Exactly how the table will be rendered is determined by the DataDisplayMode
        // and DataView data members.
        public string DataViewSourceMode { get; set; }

        // Determines what happens when the link associated with a syndication feed item is clicked.  
        // If DataDisplayMode is "Report" then ReportBaseURL below must be specified.  The link
        // associated with the feed will be the concatenation of the ReportBaseURL plus query parameters for each
        // primary key specified using the ID element.  This enables integration with Reporting Services and also
        // any other web data display technology.  The DataViewSourceMode (if specified) is ignored.

        // If DataDisplayMode is "HTML" then the link associated with the feed will generate a simple HTML table.  The
        // DataViewSourceMode attribute determines how the data is gathered (either from a table or table valued function), 
        // and DataView determines which columns are displayed.  The DisplayName member of
        // the Column object controls the title of the column.  If no DisplayName is specified, the Name of the Column
        // is used.

        // If DataDisplayMode is "XML" then the link associated with the feed will generate XML.  The XML
        // generated will have a root element named "Row".  Within this element the element names will be determined
        // by the DataView columns.  If the DisplayName of the Column element is specified in the DataView, then 
        // the DisplayName is used as the element name within the Row element, otherwise the Name attribute is used
        // to name the element.  The value of the element is determined by the contents of the column.  For example
        // the XML might look something like 
        //   <Row> <ProductReviewID>42</ProductReviewID> <ProductName>Tricycle</ProductName> </Row>
        // The source of the data (table or function) is determined by the DataViewSourceMode attribute above.
        public string DataDisplayMode { get; set; }

        // When DataViewSourceMode is "Report" then the ReportBaseURL attribute specifies the first part of 
        // the URL to use when the user clicks on the syndication feed item link.  The rest of the URL will
        // be a concatenation of the primary keys as query parameters on the URL.
        public string ReportBaseURL { get; set; }

        // The FeedTitle attribute contains literal text which is used as the title for the overall syndication feed.
        public string FeedTitle { get; set; }

        // The FeedDescription attribute contains literal text which is used as the overall description 
        // of the syndication feed.
        public string FeedDescription { get; set; }

        // The FeedAlternateUrl attribute contains literal text which is used as a general link for the syndication feed.
        public string FeedAlternateUrl { get; set; }

        // Optional ancillary tables to join to this one in order to generate syndication feed item 
        // titles and descriptions.  
        public List<ReferenceTableDescription> ReferenceTables { get; set; }


        // These data members are indirectly derived from the XML configuration file.
        // They are generated when config file changes.

        // What query to invoke when it will be necessary to ask the database for changes.
        public SqlCommand _GetChangesQuery;

        // The parsed syndication feed item title format string
        private ParsedDescriptionFormat _ChangesTitleFormat;
        // The parsed syndication feed item description format string
        private ParsedDescriptionFormat _ChangesDescriptionFormat;

        // Since it is expensive to resolve simple column names to ones resolved to a specific schema and
        // table, cache the results of that resolution.
        private Dictionary<string, string> _CachedQualifiedColumnNames = new Dictionary<string,string>();


        // If the changes filter mode is TOP n, this is the value of n
        private int _NumberOfRowsToKeep;

        // The ADO.NET object used to fill the _NewChanges data table
        private SqlDataAdapter _ChangesAdapter = new SqlDataAdapter();

        // The following data members are dynamically updated from database.
        // Be sure to have a write lock on _Lock before updating these!

        // The location where the current set of changes is stored as DataRows
        private DataTable _NewChanges = new DataTable();
        // The WCF object which will generate RSS or Atom feeds
        private SyndicationFeed _ChangesFeed = null;
        // When we should check the database again to see if there are changes.  This allows
        // for caching of results for as long as desired before hitting the database again.
        private DateTime _NextDatabaseRequest;
        // Each change has a sequence number.  This is the sequence number of the last change
        // from the database.  See Change Tracking in SQL Server Books Online for details.
        private long _LastChangeNumber;
        // Contains column names and column values for a particular row of data from the 
        // get changes query results.  These values are used to provide data to bind a format string
        // to column values for a particular row.
        private Dictionary<string,string> _RowValues = new Dictionary<string,string>();

        // When building query strings, separate the column names with a comma followed by a space.
        private const string columnSeperator = ", ";
        // The name we'll use for the Change Tracking table which describes what changes have occurred.
        public const string ChangeTableName = "TABLECHANGES";


        // The constructor which initializes this class based on the appropriate XML from 
        // the XML configuration file.  The base class will deal with some base data members then call
        // InitializeLocalMembers which will initilize additional base data members plus the data members of
        // this class.
        public TableDescription(Connection parentDataConnection, XElement tableElement) : base(parentDataConnection, tableElement)
        {
        }

        // Called by the base class constructor and by the code which manages changes in the XML
        // configuration file.
        protected override void InitializeLocalMembers(XElement tableElement, Connection parentDataConnection) 
        {
            //Initialize the base class members
            base.InitializeLocalMembers(tableElement, parentDataConnection);
            //Clear the cache of the dictionary which maps simple column names to fully qualified ones.
            _CachedQualifiedColumnNames.Clear();

            // Pull configuration information from XML elements and attributes in the XML configuration file.
            this.ChangeFilter = AttributeValueOrNull(tableElement, "ChangeFilter");
            this.ChangeFilterMode = AttributeValueOrNull(tableElement, "ChangeFilterMode");
            this.TitleMode = AttributeValueOrNull(tableElement, "TitleMode");
            this.DescriptionMode = AttributeValueOrNull(tableElement, "DescriptionMode");
            this.DataViewSourceMode = AttributeValueOrNull(tableElement, "DataViewSourceMode");
            this.DataDisplayMode = AttributeValueOrNull(tableElement, "DataDisplayMode");
            this.ReportBaseURL = AttributeValueOrNull(tableElement, "ReportBaseURL");
            this.FeedTitle = AttributeValueOrNull(tableElement, "FeedTitle");
            this.FeedDescription = AttributeValueOrNull(tableElement, "FeedDescription");
            this.FeedAlternateUrl = AttributeValueOrNull(tableElement, "FeedAlternateUrl");
            if (_ChangesFeed != null)
            {
                //If we already created a syndication feed, update some of the non-item properties
                // Maybe get fancy some day and add support for HTML and images.
                _ChangesFeed.Title = new TextSyndicationContent(this.FeedTitle);
                _ChangesFeed.Description = new TextSyndicationContent(this.FeedDescription);
                // There doesn't appear to be a way to update the alternate URL.
            }

            this.Title = ElementValueOrNull(tableElement, "Title"); 
            this.Description = ElementValueOrNull(tableElement, "Description");
            this.DateColumn = ElementValueOrNull(tableElement, "DateColumn");
            this.DataViewSource = ElementValueOrNull(tableElement, "DataViewSource");

            IEnumerable<XElement> referenceTableElements = tableElement.Elements(fm + "ReferenceTable");
            if (referenceTableElements != null)
            {
                // If this table needs to be joined with other tables when generating titles and descriptions then
                // use the name of those tables to generate a list of objects which represent those other tables, called
                // reference tables.
                this.ReferenceTables = parentDataConnection.ReferenceTables.LookupValues((referenceTableElement)
                                             => referenceTableElement.Value, referenceTableElements);
            }

            // Regenerate any queries which might be based on the XML configuration file and in general update the application state
            // that depends on the configuration files.
            RecomputeQueries();
        }

        // Update queries and any other application state which is dependent on the information in the XML configuration files.
        // Right now we just have the GetChanges query but leave open the possibility of additional queries in the future.
        private void RecomputeQueries()
        {
            ComputeGetChangesQuery();
            //Reset the feed since how the feed is presented could have radically changed.
            _LastChangeNumber = 0;
            if (_ChangesFeed != null) ((List<SyndicationItem>)_ChangesFeed.Items).Clear();
        }

        /// <summary>
        /// Merges the current table definition with a changed table definition from the XML configuration file.
        /// </summary>
        /// <param name="tableElement">The relevant portion of the XML configuration file</param>
        /// <param name="currentTableDescription">The object that represents the existing table, or null if this is a new table.</param>
        /// <param name="parentConnection">The parent object of this table which represents the server, database, 
        /// and authentication information (the connection)</param>
        /// <returns>The merged or new table description object</returns>
        public static TableDescription MergeTableDescription(XElement tableElement, TableDescription currentTableDescription, Connection parentConnection)
        {
            // If this is a new table entry in the XML configuration file, just create and return the new object which represents the table
            if (currentTableDescription == null)
                return new TableDescription(parentConnection, tableElement);
            else
            {
                // Otherwise overwrite the data members with information recorded in the XML configuration file and return the current object.
                // This is done in the context of a write lock so that no readers see a partially updated object.
                currentTableDescription.ExecuteWithLock(LockType.Write,
                    () => { currentTableDescription.InitializeLocalMembers(tableElement, parentConnection); });
                return currentTableDescription;
            }
        }

        /// <summary>
        /// Update the _GetChangesQuery data member so that it contains a query which finds out what has changed for this table.  The query 
        /// specifically gathers columns from this table and any joined tables necessary to display titles and descriptions for the syndication feed.
        /// </summary>
        private void ComputeGetChangesQuery()
        {
            _ChangesTitleFormat = null;
            _ChangesDescriptionFormat = null;

            // Begin building up a string which will be the query string to send to the server.
            StringBuilder sb = new StringBuilder();
            // Code required by the Change Tracking feature to ensure that we use the max of the last change number and the minimum valid change
            // number.
            sb.AppendLine("DECLARE @LastValidChange bigint;");
            sb.AppendFormat("SELECT @LastValidChange = CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('{0}'));", this.Name);
            sb.AppendLine("SELECT @LastValidChange = CASE WHEN @LastValidChange < @LastChangeNumber THEN @LastChangeNumber ELSE @LastValidChange END;");
            sb.Append("SELECT ");
            switch (this.ChangeFilterMode)
            {
                case "TimeInterval":
                    throw new NotImplementedException("Time interval is not yet implemented.");
                // Limit the syndication feed to the n most recent changes, where n = _NumberOfRowsToKeep
                case "Top":
                    _NumberOfRowsToKeep = int.Parse(this.ChangeFilter);
                    sb.AppendFormat("TOP {0} ", _NumberOfRowsToKeep);
                    break;

                default:
                    throw new ArgumentException(string.Format("{0} is not a valid change filter mode.",
                        this.ChangeFilterMode), "tableDescription");
            }
            
        
            // Create and output a comma delimited list of primary key columns 
            string pkString = ComputePKList(ChangeTableName);
            sb.Append(pkString);
            sb.Append(columnSeperator);
            // Output the part of the query that defines the psuedo-columns, the columns that are generated by the Change Tracking feature.
            sb.AppendFormat("{1}, {0}.SYS_CHANGE_VERSION, Operation = CASE {0}.SYS_CHANGE_OPERATION WHEN 'I' THEN 'added' WHEN 'U' THEN 'updated' WHEN 'D' THEN 'removed' END, ", 
                ChangeTableName, 
                (this.DateColumn == null) ? "GETDATE() AS OperationDate" : this.DateColumn);
            // Output the columns required for syndication item titles and descriptions.
            // Titles and descriptions for each syndication item are processed in a similar way.  We might compute them 
            // by analyzing something which looks similar to a format string (see ParsedDescriptionFormat.cs) or 
            // by invoking a scalar user defined function on the server which we pass all the primary keys.  A mode string
            // determines which approach to use, and there is a string which contains either the format information or the name
            // of the user defined function.  OutputModedString takes care of processing these two kinds of modes correctly for
            // both titles and descriptions.
            _ChangesTitleFormat = OutputModedString("title", this.TitleMode, this.Title, pkString, sb);
            sb.Append(columnSeperator);
            _ChangesDescriptionFormat = OutputModedString("description", this.DescriptionMode, this.Description, pkString, sb);
            // Invoke the Change Tracking feature from the FROM clause of the SELECT.
            sb.AppendFormat(" FROM CHANGETABLE(CHANGES {1}, @LastValidChange) AS {0} ", 
                ChangeTableName, this.Name);
            // Output any necessary joins for both this table and any reference tables
            OutputChangesJoinClause(sb);
            // Output an ORDER BY clause so that we get the results by change number in descending order.  This ensures the most
            // recents changes are the ones which are kept.
            sb.AppendFormat(" ORDER BY {0}.SYS_CHANGE_VERSION DESC;", ChangeTableName);

            // Create the ADO.NET command object with the results of building up this query string.  The query takes one parameter, which
            // is the last change number we've seen.
            _GetChangesQuery = DataConnection.Conn.CreateCommand();
            _GetChangesQuery.CommandText = sb.ToString();
            _GetChangesQuery.Parameters.Add("@LastChangeNumber", SqlDbType.Int);

        }

        /// <summary>
        ///   Titles and descriptions for each syndication item are processed in a similar way.  We might compute them 
        ///   by analyzing something which looks similar to a format string (see ParsedDescriptionFormat.cs) or 
        ///   by invoking a scalar user defined function on the server which we pass all the primary keys.  A mode string
        ///   determines which approach to use, and there is a string which contains either the format information or the name
        ///   of the user defined function.  OutputModedString takes care of processing these two kinds of modes correctly for
        ///   both titles and descriptions.
        /// </summary>
        /// <param name="stringName">Which kind of syndication item property (title or description, for example)</param>
        /// <param name="mode">Whether to use the format style of generating the result, or invoking a scalar user defined function.</param>
        /// <param name="content">Either the format string or the name of the user defined function</param>
        /// <param name="pkString">a comma separated string of the names of primary keys</param>
        /// <param name="sb">the string builder to send all the output for building the query</param>
        /// <returns></returns>
        private ParsedDescriptionFormat OutputModedString(string stringName, string mode, string content, string pkString, 
            StringBuilder sb)
        {
            ParsedDescriptionFormat parsedFormat = null;
            switch (mode)
            {
                case "Format":
                    parsedFormat = new ParsedDescriptionFormat();
                    parsedFormat.Parse(content, this.QualifiedName);
                    parsedFormat.OutputCommaDelimitedColumnNames(sb);
                    break;

                case "Function":
                    sb.Append(content);
                    sb.Append("(");
                    sb.Append(pkString);
                    sb.Append(")");
                    break;

                default:
                    throw new ArgumentException(string.Format("{0} is an invalid {1} mode.", 
                        mode, stringName));
            }
            return parsedFormat;
        }

        //Output the join clause which joins the CT change table with table changes are coming from.
        private void OutputChangesJoinClause(StringBuilder sb)
        {
            sb.AppendFormat("JOIN {0} ON ", this.Name);
 	        Utility.OutputStringJoin<ColumnDescription>("AND ", 
                this.IDColumns, 
                (column) => string.Format("{0}.{1} = {2}.{1} ", this.Name, column.Name, ChangeTableName), 
                sb);
            foreach (ReferenceTableDescription referenceTable in this.ReferenceTables)
            {
                sb.AppendFormat("JOIN {0} ON ", referenceTable.Name);
                //TODO: Assumes key columns are named the same in reference and changes table.  This is an oversimplification.
                //Allow more flexible joins in a future version.
                Utility.OutputStringJoin<ColumnDescription>("AND ", referenceTable.IDColumns,
                    (column) => string.Format("{0}.{1} = {2}.{1}", referenceTable.Name, column.Name, this.Name, column.Name), sb);
            }
        }

        //Construct a string which contains a comma-separated list of column names with the specified prefix (schema name).
        private string ComputePKList(string prefix)
        {
            StringBuilder sb = new StringBuilder();
            Utility.OutputStringJoin<ColumnDescription>(columnSeperator, this.IDColumns, (column) => prefix + '.' + column.Name, sb);
            return sb.ToString();
        }

        /// <summary>
        // This is the method the WCF web service (indirectly) invokes (via the parent objects of this table) to get the changes for this table.
        // Any caller should call this within a finally block that checks for both read and write locks 
        // and releases accordingly for _Lock.
        /// </summary>
        /// <returns>The WCF syndication feed object which contains user presentable information about changes to this table</returns>
        internal SyndicationFeed CurrentChanges()
        {
            // If it is too soon to bother checking the database for changes, just return the cached object.
            if (_ChangesFeed != null && DateTime.Now < _NextDatabaseRequest)
                return _ChangesFeed;

            // Otherwise we need to ask the database for changes.
            // Escalate lock and retry (some other thread may have beaten us to the punch)
            if (_Lock.IsReadLockHeld) _Lock.ExitReadLock();
            _Lock.EnterWriteLock();

            // If some other thread has managed to refresh from the database before us, return their work.
            if (_ChangesFeed != null && DateTime.Now < _NextDatabaseRequest)
                return _ChangesFeed;

            // Otherwise create a new feed object if necessary.
            if (_ChangesFeed == null) _ChangesFeed = new SyndicationFeed(this.FeedTitle, this.FeedDescription, new Uri(this.FeedAlternateUrl),
                new List<SyndicationItem>());

            // Clear out the data table we're going to use to fill with the current set of changes.
            _NewChanges.Clear();

            // Fill in the parameter for the query we're sending to the database for changes with the number of the last change we've seen.  The 
            // Change Tracking feature numbers changes for us.
            _GetChangesQuery.Parameters[0].Value = _LastChangeNumber;
            _ChangesAdapter.SelectCommand = _GetChangesQuery;

            // We may need to retry the query if the minimum valid change number changed during the processing of our query
            bool shouldRetry = true;

            while (shouldRetry)
            {
                shouldRetry = false;
                try
                {
                    // Try and get the latest changes.
                    _ChangesAdapter.Fill(_NewChanges);
                    if (_NewChanges.Rows.Count > 0)
                    {
                        // If there are some changes, add them to the syndication feed as new items.
                        AddChangesToFeed();
                        break;
                    }
                }
                catch (SqlException sqlEx)
                {
                    // If we got the exception because the minimum valid change number changed during the processing
                    // of our query then go ahead and retry the query.
                    if (VerifyMinimumChanges())
                    {
                        shouldRetry = true;
                        continue;
                    }
                    else
                    {
                        //Otherwise, log the error and return a pretty empty feed indicating an error.
                        foreach (SqlError err in sqlEx.Errors)
                        {
                            Logger.Current.LogMessage(EventLogEntryType.Error, "Current changes SQL error: {0}",
                                err.ToString());
                        }
                        ((List<SyndicationItem>)_ChangesFeed.Items).Clear();
                        ((List<SyndicationItem>)_ChangesFeed.Items).Add(new SyndicationItem("Error",
                            "An error has occurred.  See event log for details.", null));
                    }
                }
            }
            // Reset the cache time
            _NextDatabaseRequest = DateTime.Now.AddSeconds(DataConnection.CacheDuration);

            return _ChangesFeed;
        }

        /// <summary>
        /// See if we just happen to hit a window where the minimum valid change number changed during the processing of our query.
        /// If it has, try to recover.  Return true to our caller to indicate that the caller should retry the query.  See the 
        /// documentation on Change Tracking in SQL Server Books Online for more details.
        /// </summary>
        /// <returns>True if a retry makes sense.</returns>
        private bool VerifyMinimumChanges()
        {
            SqlCommand verifyCmd = DataConnection.Conn.CreateCommand();
            verifyCmd.CommandText = string.Format("SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('{0}'));",
                this.Name);
            long minValid = (long)(verifyCmd.ExecuteScalar());
            if (minValid > _LastChangeNumber)
            {
                _LastChangeNumber = minValid;
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// We got a list of existing syndication feed items in the _ChangesFeed object, and we've 
        /// got some new data rows in the _NewChanges data table.  Add new syndication feed items to the syndication feed
        /// based on the settings extracted from the XML configuration file and the data in the new data rows.  Remove any
        /// rows on the tail end of the feed in order to comply with the settings we got from the XML configuration file.
        /// </summary>
        private void AddChangesToFeed()
        {
            List<SyndicationItem> items = (List<SyndicationItem>)_ChangesFeed.Items;
            SyndicationItem item;
            string title;
            string description;
            string url;
            string id;
            
            DataRow dr;

            // Update the number of the last change so it keeps current.
            _LastChangeNumber = (long)(_NewChanges.Rows[0]["SYS_CHANGE_VERSION"]);

            // Iterate through the changes in reverse order.
            for (int i = _NewChanges.Rows.Count - 1; i >= 0; i--)
            {
                dr = _NewChanges.Rows[i];
                _RowValues.Clear();
                int columnNumber = 0;
                // Pull data out of each change (DataRow) one row at a time.  This information is used to 
                // compute format strings if they are used.

                // First, pull the primary columns from the row
                foreach (ColumnDescription column in this.IDColumns)
                {
                    MaybeAddColumnValue(column.Name, columnNumber, dr);
                    columnNumber += 1;
                }
                // Next, add the fixed information which comes from the CT change table
                string dateColumnName = (this.DateColumn == null) ? "OperationDate" : this.DateColumn;
                MaybeAddColumnValue(dateColumnName, columnNumber++, dr);
                MaybeAddColumnValue(ChangeTableName + ".SYS_CHANGE_VERSION", columnNumber++, dr);
                MaybeAddColumnValue("Operation", columnNumber++, dr);

                // Next, pull the title and description column information necessary for the format strings if they were used
                // and compute the format strings.  If a function was used instead of a format string, get the results of the 
                // function call.  Return either the results of binding the format string to the row values, or the value of 
                // the function call depending on the mode the user selected.

                title = CaptureModeStringData(dr, ref columnNumber, _ChangesTitleFormat);
                description = CaptureModeStringData(dr, ref columnNumber, _ChangesDescriptionFormat);
                url = ComputeItemUrl();
                id = ComputeID();

                // Create the new syndication feed item and add it to the feed.
                item = new SyndicationItem(title, description, new Uri(url), id, DateTimeOffset.Parse(_RowValues[dateColumnName]));
                items.Insert(0, item);
            }
            // Remove items which should not be published in the feed anymore.
            if (items.Count > _NumberOfRowsToKeep) items.RemoveRange(_NumberOfRowsToKeep, items.Count - _NumberOfRowsToKeep);
        }

        /// <summary>
        /// Helper function that pulls non-null data out of a column in the data row (if present in the data row) and adds it
        /// to the dictionary that will be used to bind row values to the title and description format strings.
        /// </summary>
        /// <param name="columnName">The name to use as the key for the row value in the _RowValues dictionary</param>
        /// <param name="columnNumber">The expected column number of the particular row value</param>
        /// <param name="dr">The data row to extract the row value from</param>
        private void MaybeAddColumnValue(string columnName, int columnNumber, DataRow dr)
        {
            object columnValue;
			if (!_RowValues.ContainsKey(columnName))
			{
				columnValue = dr[columnNumber];
				if (columnValue != null && columnValue != System.DBNull.Value)
                {
                    _RowValues.Add(columnName, columnValue.ToString());
                }
			}
        }

        /// <summary>
        /// If we're presenting a title or description from data returned by a scalar UDF, just pull the result of calling the UDF out of the 
        /// data row and return that.  If we're presenting a title or description from a format string, then we need to gather up potentially
        /// several columns worth of data into a dictionary (_RowValues) and then bind the format string to the values in the dictionary.  The result
        /// of filling in the format string with the data from the data row is returned.  The column number we're processing is also updated.
        /// </summary>
        /// <param name="dr">The data row contain the data we need</param>
        /// <param name="columnNumber">The column number to start reading from.  This gets updated to be past all the columns we've read.</param>
        /// <param name="parsedFormat">The parse tree of the format string to use, or null if we're just calling a scalar UDF.</param>
        /// <returns>A title or description string for the syndication feed item.</returns>
        private string CaptureModeStringData(DataRow dr, ref int columnNumber,
            ParsedDescriptionFormat parsedFormat)
        {
            if (parsedFormat == null)
            {
                return dr[columnNumber++].ToString();
            }
            else
            {
                foreach (string columnName in parsedFormat.ColumnNames)
                {
                    MaybeAddColumnValue(columnName, columnNumber++, dr);
                }
                return parsedFormat.Bind(_RowValues);
            }
        }

        // Returns the URL for the syndication feed item.
        private string ComputeItemUrl()
        {
            switch (this.DataDisplayMode)
            {
                case "HTML":
                    throw new ArgumentException("HTML display mode not yet implemented");

                case "XML":
                    throw new ArgumentException("XML display mode not yet implemented");

                case "Report":
                    if (ReportBaseURL == null)
                        throw new ArgumentException("ReportBaseURL must be specified for Data Display Mode of Report.");
                    // Add primary keys to the URL as query parameters to aid the web page which will be displaying the data for this syndication item.
                    StringBuilder sb = new StringBuilder(this.ReportBaseURL);
                    foreach (ColumnDescription column in IDColumns)
                    {
                        //Primary keys need to be present.
                        sb.AppendFormat("&{0}={1}", column.Name, _RowValues[column.Name]);
                    }
                    return sb.ToString();
                default:
                    throw new ArgumentException(string.Format("{0} is an unknown Data Display Mode.", this.DataDisplayMode));
            }

        }

        // Return a unique id as a string for this syndication item based on the primary key column values plus a separator between each column value.
        private string ComputeID()
        {
            string seperator = (this.IDSeparator == null) ? "|" : this.IDSeparator;
            StringBuilder sb = new StringBuilder();
            // Output the values of the primary keys as a string separated by specified value (or '|' by default).
            Utility.OutputStringJoin<ColumnDescription>(seperator, this.IDColumns, (column) => _RowValues[column.Name], sb);
            return sb.ToString();
        }

        // Format strings refer to simple (unqualified) column names like "ProductName".  These simple names may be logical or physical.  
        // This method determine a fully qualified name for the column which includes schema and table name.
        public string QualifiedName(string columnName)
        {
            //If this is a special (predefined) column name, just return it.  No qualification is necessary.  Psuedo-columns need no qualification.
            if (ParsedDescriptionFormat.PredefinedColumns.Contains<string>(columnName, StringComparer.InvariantCultureIgnoreCase))
                return columnName;
            string result = null;
            //If we can't find the column in the cache, then figure out which table this column is in,
            //compute the fully qualified name, cache it, and return it.  Otherwise just return what we found in the cache.
            if (!_CachedQualifiedColumnNames.TryGetValue(columnName, out result))
            {
                //If the column is in this table's data view, qualify the column with this table name.
                foreach (ColumnDescription cd in this.DataViewColumns)
                {
                    if (cd.IsGeneralNameMatch(columnName))
                    {
                        result = string.Format("{0}.{1}", this.Name, cd.Name);
                        break;
                    }
                }

                //If the column isn't explicitly in this tables data view, check any referenced tables and qualify them
                //with the referenced table's name if found.
                if (result == null)
                {
                    foreach (ReferenceTableDescription referenceTable in this.ReferenceTables)
                    {
                        foreach (ColumnDescription cd in referenceTable.DataViewColumns)
                        {
                            if (cd.IsGeneralNameMatch(columnName))
                            {
                                result = string.Format("{0}.{1}", referenceTable.Name, cd.Name);
                            }
                        }
                    }
                }

                //If no specific column is mentioned in our data view or referenced data views then we'll assume the column
                //is in this table.
                if (result == null) result = string.Format("{0}.{1}", this.Name, columnName);

                //Cache the result so future lookups will be faster.
                _CachedQualifiedColumnNames[columnName] = result;
            }
            return result;
        }



    }


}
